﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace customer.dao.V2
{
    public class SqlServerDBEngine : DBEngineBase
    {

        public SqlServerDBEngine(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        public override GetResult Get(string sql)
        {
            GetResult result = new GetResult();

            try
            {   
                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    SqlDataAdapter adapter = null;

                    DataTable dtResult = new DataTable();


                    adapter = new SqlDataAdapter(sql, conn);

                    adapter.Fill(dtResult);

                    List<Dictionary<string, object>> items = DATA_Util.DataTable2Dictionary(dtResult);

                    if (items.Count > 0)
                        result.Item = items[0];
                }

                result.Success =true;
            }
            catch (Exception ex)
            {   
                result.Success = false;
                result.Message = ex.ToString();
            }

            return result;
        }


        public override QueryResult Query(string sql, string pk, int pageIndex, int pageSize)
        {
            QueryResult result = new QueryResult();

            try
            {
                string totalSQL = "select count(*) from ( " + sql + " ) as A";

                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {
                    conn.Open();
                    
                    SqlDataAdapter adapter = null;

                    DataTable dtResult = new DataTable();

                    string tableName = string.Empty;
                    if (pk.Equals("0"))
                        tableName = ParseTableName(sql);

                    if (string.IsNullOrWhiteSpace(tableName) == false)
                    {   
                        string sql_pk = string.Format("SELECT top 1 COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE TABLE_NAME='{0}'", tableName);

                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = sql_pk;
                            var scalar = cmd.ExecuteScalar();
                            if (scalar != null)
                                pk = scalar.ToString();
                        }
                    }


                    if (pageIndex > 0 && pageSize > 0 && pk.Equals("0") == false)
                    {
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = totalSQL;
                            result.Total = int.Parse(cmd.ExecuteScalar().ToString());
                        }
                        
                        int firstIndex = (pageIndex - 1) * pageSize;

                        string pageSQL = string.Empty;

                        pageSQL = "SELECT * FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY " + pk + ") AS RowNumber FROM (select 0 as __tc__, * from (" + sql + ") t) a) DataPage ";

                        pageSQL += " WHERE RowNumber > " + firstIndex + " AND RowNumber <= " + (firstIndex + pageSize);

                        adapter = new SqlDataAdapter(pageSQL, conn);

                        adapter.Fill(dtResult);
                        
                        result.PageNo = pageIndex;
                    }
                    else
                    {
                        adapter = new SqlDataAdapter(sql, conn);
                        adapter.Fill(dtResult);
                        result.Total = dtResult.Rows.Count;
                    }
                    
                    result.Items = DATA_Util.DataTable2Dictionary(dtResult).ToArray();
                }

                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }

            return result;
        }

        string ParseTableName(string sql)
        {
            string tableName = string.Empty;
            try
            {
                Regex reg = null;
                reg = new Regex(@"\s+from\s+.*?(\s+where\s+|\s+order\s+|\s+group\s+)|\s+from\s+.+", RegexOptions.IgnoreCase);
                tableName = reg.Match(sql).Value;
                tableName = Regex.Replace(tableName.ToLower(), @"\s+from\s+|\s+where\s+|\[|\]|\s+order\s+|\s+group\s+", "");
            }
            catch (Exception ex)
            { }
            return tableName;
        }

        public override ScaleResult Scale(string sql)
        {
            ScaleResult result = new ScaleResult();

            try
            {   
                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        result.Scalar = cmd.ExecuteScalar();
                    }
                }

                result.Success = true;
            }
            catch (Exception ex)
            {   
                result.Success = false;
                result.Message = ex.ToString();
            }

            return result;
        }


        public override WriteResult Write(string sql)
        {
            WriteResult result = new WriteResult();
            try
            {
                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        result.rows = cmd.ExecuteNonQuery();
                    }
                }
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }
            return result;
        }

        public override DataResult Test()
        {
            DataResult result = new DataResult();
            try
            {
                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {   
                    conn.Open();

                }
                result.Success = true;
            }
            catch (Exception ex)
            {   
                result.Success = false;
                result.Message = ex.ToString();
            }
            return result;
        }
    }
}
